﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.OleDb;
using System.IO;
using System.Data;
using System.Globalization;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using Citi.SecurityHelper;
using System.Runtime.InteropServices;
using System.Diagnostics;
using Microsoft.Office.Core;
using System.Runtime.CompilerServices;

namespace Citi.Helpers
{
    public class ExcelHelper
    {
        public static bool RunOnBackground { get; set; }

        public static bool ValidateExcelFile(string file, string pwdFile)
        {
            Application excel = null;
            Workbook workbook = null;

            excel = GetApplication();
            excel.DisplayAlerts = true;
            workbook = GetWorkbook(file, pwdFile, excel);
            FinalizeExcel(workbook, excel);
            return true;
        }

        public static string ConvertXlsToXlsb(string pathXls, string pathXlsb)
        {
            if (Path.GetExtension(pathXls) == ".xlsb") return pathXls;

            if (File.Exists(pathXlsb)) File.Delete(pathXlsb);

            var excel = GetApplication();
            //excel.Visible = false;

            try
            {
                var workbook = excel.Workbooks.Open(pathXls, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                workbook.SaveAs(pathXlsb, XlFileFormat.xlExcel12, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //eWorkbook.SaveAs(pathXlsb, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing,
                //    Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                workbook.Close(false, Type.Missing, Type.Missing);
                Marshal.ReleaseComObject(workbook);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                excel.Quit();
                Marshal.ReleaseComObject(excel);
                System.GC.Collect();
                KillExcelProcess(excel);
            }

            return pathXlsb;
        }

        private static void KillExcelProcess(Application excel)
        {
            try
            {
                uint processId;
                GetWindowThreadProcessId(new IntPtr(excel.Hwnd), out processId);

                if (processId != 0)
                {
                    var excelProcess = Process.GetProcessById((int)processId);
                    excelProcess.CloseMainWindow();
                    excelProcess.Refresh();
                    excelProcess.Kill();
                }
            }
            catch { }
        }

        [DllImport("user32.dll", SetLastError = true)]
        static extern uint GetWindowThreadProcessId(IntPtr hWnd, out uint lpdwProcessId);

        public static string ConvertXlsToXlsx(string pathXls, string pathXlsx)
        {
            if (Path.GetExtension(pathXls) == ".xlsx") return pathXls;

            if (File.Exists(pathXlsx)) File.Delete(pathXlsx);

            var excel = GetApplication();
            //excelApp.Visible = false;
            var workbook = GetWorkbook(pathXls, null, excel);

            try
            {
                //var eWorkbook = excel.Workbooks.Open(pathXls, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                //    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                workbook.SaveAs(pathXlsx, Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                //eWorkbook.Close(false, Type.Missing, Type.Missing);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                FinalizeExcel(workbook, excel);
                //CloseApplication(excel);
                ////excel.Quit();
                //Marshal.ReleaseComObject(excel);
                //System.GC.Collect();
                //KillExcelProcess(excel);
            }

            return pathXlsx;
        }

        public static string ConvertXlsToXlsx(string path)
        {
            if (Path.GetExtension(path) == ".xlsx") return path;

            var pathXlsx = path.Replace(".xls", ".xlsx");
            return ConvertXlsToXlsx(path, pathXlsx);
        }

        public static void RefreshAll(string xlsFile, string pwdFile = null, bool removeDataLinks = false, bool transformTableToRange = false)
        {
            var excelAplication = GetApplication();
            var workbook = GetWorkbook(xlsFile, pwdFile, excelAplication);

            try
            {
                excelAplication.AskToUpdateLinks = false;

                workbook.RefreshAll();

                if (removeDataLinks)
                    RemoveDataLinks(workbook);

                if (transformTableToRange)
                    TransformTableToRange(workbook);

                workbook.Save();
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                FinalizeExcel(workbook, excelAplication);
            }
        }

        private static Application GetApplication()
        {
            var excelAplication = new Excel.Application();
            excelAplication.DisplayAlerts = false;
            excelAplication.ODBCTimeout = int.MaxValue;

            return excelAplication;
        }

        public static void GetPivotTableImage(string xlsFile, string pivotTableName, string sheetName, string bmpPathToSave, string pwdFile = null)
        {
            var excelAplication = GetApplication();
            var workbook = GetWorkbook(xlsFile, pwdFile, excelAplication);

            try
            {
                excelAplication.AskToUpdateLinks = false;
                var worksheet = (Excel.Worksheet)workbook.Worksheets[sheetName];
                var pivotTable = (Excel.PivotTable)worksheet.PivotTables(pivotTableName);
                var range = pivotTable.TableRange2;
                range.CopyPicture(XlPictureAppearance.xlScreen, XlCopyPictureFormat.xlBitmap);

                var charts = worksheet.ChartObjects();
                var cht = charts.Add(0, 0, range.Width + 10, range.Height + 10);

                cht.Chart.Paste();
                cht.Chart.Export(bmpPathToSave);
                cht.Delete();
            }
            finally
            {
                FinalizeExcel(workbook, excelAplication);
            }
        }

        private static void TransformTableToRange(Workbook workbook)
        {
            for (int i = 1; i < workbook.Worksheets.Count + 1; i++)
            {
                for (int j = 1; j < workbook.Worksheets[i].ListObjects.Count + 1; j++)
                {
                    try
                    {
                        //workbook.Worksheets[i].ListObjects[j].ConvertToRange();
                        workbook.Worksheets[i].ListObjects[j].Unlist();
                    }
                    catch
                    {
                    }
                }
            }
        }

        public static void TransformTableToRange(string xlsFile, string[] tableNames, string pwdFile = null)
        {
            var excelAplication = GetApplication();
            var workbook = GetWorkbook(xlsFile, pwdFile, excelAplication);

            try
            {
                excelAplication.AskToUpdateLinks = false;

                for (int i = 1; i < workbook.Worksheets.Count + 1; i++)
                {
                    for (int j = 1; j < workbook.Worksheets[i].ListObjects.Count + 1; j++)
                    {
                        try
                        {
                            if (tableNames == null || !tableNames.Any() || tableNames.Contains(((ListObject)workbook.Worksheets[i].ListObjects[j]).Name))
                                workbook.Worksheets[i].ListObjects[j].Unlist();

                            //workbook.Worksheets[i].ListObjects[j].ConvertToRange();
                        }
                        catch
                        {
                        }
                    }
                }

                workbook.Save();
            }
            finally
            {
                FinalizeExcel(workbook, excelAplication);
            }
        }

        private static void FinalizeExcel(Workbook workbook, Application excelAplication)
        {
            var nullValue = System.Reflection.Missing.Value;

            if (workbook != null)
                CloseWorkbook(workbook);

            if (excelAplication != null)
                CloseApplication(excelAplication);

            //GC.SuppressFinalize(this);

            try
            {
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            catch { }

            try
            {
                var process = GetExcelProcess(excelAplication);
                process.Kill();
            }
            catch
            {
            }
        }

        [DllImport("user32.dll")]
        static extern int GetWindowThreadProcessId(int hWnd, out int lpdwProcessId);

        static Process GetExcelProcess(Excel.Application excelApp)
        {
            int id;
            GetWindowThreadProcessId(excelApp.Hwnd, out id);
            return Process.GetProcessById(id);
        }

        private static void CloseApplication(Application excelAplication)
        {
            try
            {
                excelAplication.Quit();
            }
            catch
            {
                try
                {
                    excelAplication.Quit();
                }
                catch { }
            }

            //try
            //{
            //    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelAplication);
            //}
            //catch
            //{
            //    try
            //    {
            //        System.Runtime.InteropServices.Marshal.ReleaseComObject(excelAplication);
            //    }
            //    catch { }
            //}
        }

        private static void CloseWorkbook(Workbook workbook)
        {
            var nullValue = System.Reflection.Missing.Value;

            try
            {
                workbook.Close(false, nullValue, nullValue);
            }
            catch
            {
                try
                {
                    workbook.Close();
                }
                catch { }
            }

            workbook = null;
        }

        private static Workbook GetWorkbook(string xlsFile, string pwdFile, Application excelApplication)
        {
            var nullValue = System.Reflection.Missing.Value;
            var workBook = !string.IsNullOrEmpty(pwdFile)
                        ? SecureExcel.GetSecureWorkbook(xlsFile, pwdFile, excelApplication)
                        : excelApplication.Workbooks.Open(xlsFile, nullValue, false, nullValue, nullValue, nullValue, true, nullValue, nullValue, true, false,
                            nullValue, nullValue, nullValue, nullValue);

            if (!RunOnBackground)
            {
                foreach (Worksheet ws in workBook.Worksheets)
                {
                    foreach (WorkbookConnection cnn in workBook.Connections)
                    {
                        if (cnn.Type.ToString() == "xlConnectionTypeODBC")
                        {
                            cnn.ODBCConnection.BackgroundQuery = false;
                        }
                        else
                        {
                            cnn.OLEDBConnection.BackgroundQuery = false;
                        }
                    }
                }
            }

            return workBook;
        }

        private static void RemoveDataLinks(Workbook workbook)
        {
            while (workbook.Connections.Count > 0)
            {
                try
                {
                    workbook.Connections[1].Delete();
                }
                catch { }
            }
        }

        public static void RemoveDataLinks(string xlsFile, string[] connectionsNames, string pwdFile = null)
        {
            var excelAplication = GetApplication();
            var workbook = GetWorkbook(xlsFile, pwdFile, excelAplication);

            try
            {
                excelAplication.AskToUpdateLinks = false;
                for (int i = 1; i < workbook.Connections.Count; i++)
                {
                    try
                    {
                        if (connectionsNames == null || !connectionsNames.Any() || connectionsNames.Contains(workbook.Connections[i].Name))
                            workbook.Connections[i].Delete();
                    }
                    catch { }
                }
                workbook.Save();
            }
            finally
            {
                FinalizeExcel(workbook, excelAplication);
            }
        }

        public static string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;

            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }

            return columnName;
        }

        public static void MergeSheets(string sourceFile, string destFile, string sheetsToMergeStartsWith, string newSheetName, int headerRowCount, int firstColumnNumber)
        {
            destFile = destFile.Replace(Path.GetExtension(destFile), ".xlsx");
            var tempFile = string.Format(@"C:\Temp\{0}.xlsx", Guid.NewGuid());
            ConvertXlsToXlsx(sourceFile, tempFile);
            sourceFile = tempFile;

            var application = GetApplication();
            var sourceBook = GetWorkbook(sourceFile, null, application);
            var columnEnd = GetColumnEndName(headerRowCount, firstColumnNumber, sourceBook);

            try
            {
                Worksheet targetSheet = sourceBook.Worksheets.Cast<Worksheet>().Where(x => sheetsToMergeStartsWith == null || x.Name.StartsWith(sheetsToMergeStartsWith)).FirstOrDefault();

                if (targetSheet == null)
                    throw new Exception(string.Format("There're no sheets named starting with {0}", sheetsToMergeStartsWith));

                var firstColumn = ExcelHelper.GetExcelColumnName(firstColumnNumber);
                var rowCount = targetSheet.UsedRange.Rows.Count;

                foreach (Excel.Worksheet sheet in sourceBook.Worksheets.Cast<Worksheet>()
                    .Where(x => (sheetsToMergeStartsWith == null || x.Name.StartsWith(sheetsToMergeStartsWith))
                        && x.Name != targetSheet.Name))
                {
                    int sheetRowCount = sheet.UsedRange.Rows.Count;
                    Excel.Range workSheetRange = sheet.get_Range(string.Format("{0}{1}", firstColumn, headerRowCount), string.Concat(columnEnd, sheetRowCount));
                    workSheetRange.Copy(Type.Missing);
                    Excel.Range pasteArea = targetSheet.get_Range(string.Concat(GetExcelColumnName(firstColumnNumber), rowCount + 1),
                        string.Concat(GetExcelColumnName(workSheetRange.Columns.Count), rowCount + workSheetRange.Rows.Count));
                    pasteArea.PasteSpecial();
                    targetSheet.Paste(Type.Missing, Type.Missing);
                    rowCount += workSheetRange.Rows.Count;
                }

                foreach (Worksheet worksheet in application.Worksheets.Cast<Worksheet>().Where(x => x.Name != targetSheet.Name))
                    worksheet.Delete();

                targetSheet.Name = newSheetName;

                var rng = (Range)targetSheet.Cells[1, 1];
                rng.Select();

                sourceBook.Saved = true;
                //sourceBook.SaveCopyAs(destFile);
                sourceBook.Save();
                File.Copy(tempFile, destFile, true);
            }
            finally
            {
                FinalizeExcel(sourceBook, application);
            }

            File.Delete(tempFile);
        }

        private static string GetColumnEndName(int headerRowCount, int firstColumnNumber, Workbook sourceBook)
        {
            var columnEnd = string.Empty;
            var sheetSource = sourceBook.Worksheets[1];
            var row = sheetSource.Rows[headerRowCount];

            for (var i = firstColumnNumber; i <= row.Cells.Count; i++)
            {
                var lastCell = sheetSource.Cells[headerRowCount, i] as Range;
                var nextCell = sheetSource.Cells[headerRowCount, i + 1] as Range;

                if ((lastCell.Value2 == null || string.IsNullOrWhiteSpace(lastCell.Value2.ToString()))
                    && (nextCell.Value2 == null || string.IsNullOrWhiteSpace(nextCell.Value2.ToString())))
                {
                    columnEnd = GetExcelColumnName(i - 1);
                    break;
                }
            }
            return columnEnd;
        }

        public static System.Data.DataTable GetData(string connectionString, string query, string sheetName)
        {
            var connection = new OleDbConnection(connectionString);
            connection.Open();

            var dataTable = new System.Data.DataTable();

            try
            {
                using (connection)
                {
                    var command = new OleDbCommand(query, connection) { CommandTimeout = 20000 };
                    //var reader = command.ExecuteReader();

                    var adapter = new OleDbDataAdapter(command);
                    adapter.Fill(dataTable);
                }
            }
            catch (Exception)
            {
                connection.Close();
                throw;
            }

            return dataTable;
        }
    }
}
